<?php

namespace app\api\controller\audience;

use app\api\controller\common\ApiBase;
use Think\Db;
use think\Controller; // dengh quxiao
// Controller  ApiBase （dengl）
class Statisticsyd extends Controller
{
    public function _initialize()
    {
        parent::_initialize();
        $this->admin = session('admin');
    }

    public function getTabName($exhibition_id)
    {
        $result = db('exhibition')->where(['id' => $exhibition_id])->find();
        $tabName = '';
        if ($result) {
            $tabName = $result['create_person_table'];
        } else {
            return json(['code' => 0, 'msg' => '缺少关键字段', 'data' => '']);
        }
        return $tabName;
    }

    public function index()
    {
        $page = $this->request->param('page');
        $page = max(1, intval($page));
        $pagesize = $this->request->param('pageSize') == "" ? 20 : $this->request->param('pageSize');
        $exhibition_id = $this->request->param('exhibition_id');
        $project_id = $this->request->param('project_id');
        $start_time = $this->request->param('start_time'); //开始时间
        $end_time = $this->request->param('end_time'); //结束时间
        $channel_id = $this->request->param('channel_id'); //渠道来源
        $industrys = $this->request->param('industrys'); //用户类别
        $position_cn = $this->request->param('position_cn'); //用户职级
        $province = $this->request->param('province'); //区域
        $is_old = $this->request->param('is_old'); //是否新观众
        $keyword = $this->request->param('keyword'); //是否新观众
        if (empty($project_id)) {
            return json(['code' => 0, 'msg' => '缺少关键字段', 'data' => '']);
        }
        if (empty($exhibition_id)) {
            return json(['code' => 1, 'msg' => '展会数据错误', 'data' => ['list' => [], 'total' => 0]]);
        }
        $tabName = $this->getTabName($exhibition_id);
        //$allExList=db('exhibition')->where(['is_delete'=>0,'project_id'=>$project_id])->select();
        //查询展会里面的所有人员表格
        // $whereArr['is_delete'] = 0;
        // $whereArr['create_person_table'] = array('neq',$tabName);
        // $person_table=db('exhibition')->where($whereArr)->select();
        // $per_arr_name=array();
        // $per_arr_select=array();
        // $num=0;
        // foreach ($person_table as $v){
        //    $per_arr_name[]='crm_'.$v['create_person_table']." as z".$num;
        //    $per_arr_select[]="z".$num.".id as id".$num.", z".$num.".company_name as company_name".$num;
        //    $num++;
        // }
        // $per_tab_name=implode(',',$per_arr_name);
        // $per_select_param=implode(',',$per_arr_select);

        $param = "";
        if (!empty($start_time) && !empty($end_time)) {
            $start_year = strtotime($start_time . " 00:00:00");
            $end_year = strtotime($end_time . " 23:59:59");
            $param .= " and g.create_time>=$start_year and g.create_time<=$end_year ";
        }

        if (!empty($channel_id)) {
            if ($channel_id == '-1') {
                $param .= " and g.channel_id is null ";
            } else {
                $param .= " and g.channel_id=$channel_id ";
            }
        }

        if (!empty($industrys)) {
            if ($industrys == '-1') {
                $param .= " AND g.industrys is null ";
            } else {
                $param .= " and g.industrys like '%$industrys%' ";
            }
        }

        if (!empty($position_cn)) {
            if ($position_cn == '-1') {
                $param .= " AND g.positions_cn is null ";
            } else {
                $param .= " and g.positions_cn like '%$position_cn%' ";
            }
        }

        if (!empty($province)) {
            if ($province == '-1') {
                $param .= " AND g.province is null ";
            } else {
                $param .= " and g.province like '%$province%' ";
            }
        }

        if ($is_old > -1) {
            $param .= " and g.is_old=$is_old ";
        }

        if (!empty($keyword)) {
            $param .= " and (g.company_name like '%$keyword%' or g.name_cn like '%$keyword%') ";
        }
        //$num1=0;
        //$sql="select g.*,".$per_select_param.",q.title as channel_title from crm_".$tabName." as g ";
        $sql = "select g.*,q.title as channel_title,b.name_cn as parent_name from crm_" . $tabName . " as g ";
        $sql .= " left join crm_zsqudao as q on q.id=g.channel_id";
        $sql .= " left join crm_company_user as b on b.id=g.parent_id";
        // foreach ($person_table as $vv){
        //     $sql.=" left join crm_".$vv['create_person_table']." as z".$num1." on z".$num1.".parent_id=g.parent_id ";
        //     $num1++;
        //  }
        $sql .= " where 1=1 $param ";
        $sql .= " order by g.id desc";
        //  var_dump($sql);die();

        $sql .= " limit " . ($page - 1) * $pagesize . ",$pagesize";
        $list = Db::query($sql);
        $sql21 = " select count(g.id) as count1 ";
        $sql21 .= " from crm_" . $tabName . " as g where 1=1 $param ";
        $total1 = Db::query($sql21);
        $total = $total1[0]['count1']; //st
        return json(['code' => 1, 'msg' => '查询成功', 'data' => ['list' => $list, 'total' => $total]]);
    }


    //项目列表
    public function getProject()
    {
        $list = db('project')->where(['is_delete' => 0])->select();
        return json(['code' => 1, 'msg' => '查询成功', 'data' => $list]);
    }

    //项目下面的展会
    public function getExhList()
    {
        $project_id = $this->request->param('project_id');
        if (empty($project_id)) {
            return json(['code' => 0, 'msg' => '缺少关键字段', 'data' => '']);
        }
        $list = db('exhibition')->where(['is_delete' => 0, 'project_id' => $project_id])->select();
        foreach ($list as &$vv) {
            $year = explode('-', $vv['endtime']);
            $vv['ex_year'] = is_array($year) ? $year[0] : '';
        }
        return json(['code' => 1, 'msg' => '查询成功', 'data' => $list]);
    }

    //总渠道统计
    public function channelTotal()
    {
        $exhibition_id = $this->request->param('exhibition_id');
        if (empty($exhibition_id)) {
            return json(['code' => 0, 'msg' => '缺少关键字段', 'data' => '']);
        }
        $tabName = $this->getTabName($exhibition_id);
        $sql = "SELECT DISTINCT g.channel_id,q.title FROM crm_" . $tabName . " AS g LEFT JOIN crm_zsqudao AS q ON q.id=g.channel_id WHERE g.channel_id IS NOT null";
        $list = Db::query($sql);
        return json(['code' => 1, 'msg' => '查询成功', 'data' => $list]);
    }
    //总职务统计
    public function positionTotal()
    {
        $exhibition_id = $this->request->param('exhibition_id');
        if (empty($exhibition_id)) {
            return json(['code' => 0, 'msg' => '缺少关键字段', 'data' => '']);
        }
        $tabName = $this->getTabName($exhibition_id);
        $sql = "SELECT DISTINCT g.positions_cn FROM crm_" . $tabName . " AS g  WHERE g.positions_cn IS NOT null";
        $list = Db::query($sql);
        return json(['code' => 1, 'msg' => '查询成功', 'data' => $list]);
    }
    //总企业用户类别统计
    public function industrysTotal()
    {
        $exhibition_id = $this->request->param('exhibition_id');
        if (empty($exhibition_id)) {
            return json(['code' => 0, 'msg' => '缺少关键字段', 'data' => '']);
        }
        $tabName = $this->getTabName($exhibition_id);
        $sql = "SELECT DISTINCT g.industrys FROM crm_" . $tabName . " AS g  WHERE g.industrys IS NOT null";
        $list = Db::query($sql);
        return json(['code' => 1, 'msg' => '查询成功', 'data' => $list]);
    }
    //总区域统计
    public function provinceTotal()
    {
        $exhibition_id = $this->request->param('exhibition_id');
        if (empty($exhibition_id)) {
            return json(['code' => 0, 'msg' => '缺少关键字段', 'data' => '']);
        }
        $tabName = $this->getTabName($exhibition_id);
        $sql = "SELECT DISTINCT g.province FROM crm_" . $tabName . " AS g  WHERE g.province IS NOT null";
        $list = Db::query($sql);
        return json(['code' => 1, 'msg' => '查询成功', 'data' => $list]);
    }

    //统计渠道下面的所有总数
    public function channelEachart()
    {
        $exhibition_id = $this->request->param('exhibition_id');
        if (empty($exhibition_id)) {
            return json(['code' => 0, 'msg' => '缺少关键字段', 'data' => '']);
        }
        $tabName = $this->getTabName($exhibition_id);
        $sql = "SELECT DISTINCT g.channel_id,q.title FROM crm_" . $tabName . " AS g LEFT JOIN crm_zsqudao AS q ON q.id=g.channel_id WHERE g.channel_id IS NOT null AND is_beforehand=1";
        $list = Db::query($sql);
        //查渠道有的
        $ids = array();
        foreach ($list as &$vv) {
            $vv['total'] = db($tabName)->where(['channel_id' => $vv['channel_id']])->count();
            $ids[] = $vv['channel_id'];
        }
        unset($vv);
        $sql1 = "select count(id) as total from crm_" . $tabName;
        if(count($ids)>0){
            $ids = implode(',', $ids);
            $sql1.=" where (channel_id not in(" . $ids . ") or channel_id is null) AND is_beforehand=1";
        }else{
            $sql1.=" where channel_id is null AND is_beforehand=1";
        }
        $total1 = Db::query($sql1);
        $total = $total1[0]['total']; //st
        $allTotal=db($tabName)->count();
        array_push($list, ['channel_id' => -1, 'total' => $total, 'title' => '其它']);
        return json(['code' => 1, 'msg' => '查询成功', 'data' => $list,'total'=>$allTotal]);
    }

    //总职务柱状图统计
    public function positionEachart()
    {
//        return json(['code' => 1, 'msg' => '查询成功', 'data' => '','total'=>'']);
        $exhibition_id = $this->request->param('exhibition_id');
        if (empty($exhibition_id)) {
            return json(['code' => 0, 'msg' => '缺少关键字段', 'data' => '']);
        }
        $tabName = $this->getTabName($exhibition_id);
        $positionArr=[
            ['positions_cn'=>'董事长','total'=>0],
            ['positions_cn'=>'总裁','total'=>0],
            ['positions_cn'=>'总裁助理','total'=>0],
            ['positions_cn'=>'总经理','total'=>0],
            ['positions_cn'=>'副总经理','total'=>0],
            ['positions_cn'=>'总经理助理','total'=>0],
            ['positions_cn'=>'总监','total'=>0],
            ['positions_cn'=>'经理','total'=>0],
            ['positions_cn'=>'主管','total'=>0],
            ['positions_cn'=>'专员','total'=>0],
            ['positions_cn'=>'员工','total'=>0],
            ['positions_cn'=>'助理','total'=>0],
        ];
        $positionTotal = 0;
        foreach ($positionArr as &$vv) {
            $totals=db($tabName)->where(["positions_cn"=>["like","%".$vv['positions_cn']."%"]])->count();
            $vv['total']=$totals;
            $positionTotal+=$vv['total'];
        }
        unset($vv);
        $allTotal=db($tabName)->where(['is_beforehand'=>1])->count();
        return json(['code' => 1, 'msg' => '查询成功', 'data' => $positionArr,'total'=>$allTotal]);
    }
    //总企业用户类别统计
    public function industrysEachart()
    {
        $exhibition_id = $this->request->param('exhibition_id');
        if (empty($exhibition_id)) {
            return json(['code' => 0, 'msg' => '缺少关键字段', 'data' => '']);
        }
        $tabName = $this->getTabName($exhibition_id);
        $sql = "SELECT DISTINCT g.industrys FROM crm_" . $tabName . " AS g  WHERE g.industrys IS NOT null AND g.is_beforehand=1";
        $list = Db::query($sql);
        $ids = array();
        foreach ($list as &$vv) {
            $vv['total'] = db($tabName)->where(['industrys' => $vv['industrys']])->count();
            $ids[] = "'" . $vv['industrys'] . "'";
        }
        unset($vv);

        $sql1 = "select count(id) as total from crm_" . $tabName;
        if(count($ids)>0){
            $ids = implode(',', $ids);
            $sql1.=" where (industrys not in(" . $ids . ") or industrys is null) AND is_beforehand=1";
        }else{
            $sql1.=" where industrys is null AND is_beforehand=1";
        }
        $total1 = Db::query($sql1);
        $total = $total1[0]['total']; //st
        array_push($list, ['total' => $total, 'industrys' => '其它']);
        $allTotal=db($tabName)->count();
        return json(['code' => 1, 'msg' => '查询成功', 'data' => $list,'total'=>$allTotal]);
    }
    //总区域统计
    public function provinceEachart()
    {
        $exhibition_id = $this->request->param('exhibition_id');
        if (empty($exhibition_id)) {
            return json(['code' => 0, 'msg' => '缺少关键字段', 'data' => '']);
        }
        $tabName = $this->getTabName($exhibition_id);
        $sql = "SELECT DISTINCT g.province FROM crm_" . $tabName . " AS g  WHERE g.province IS NOT null AND g.is_beforehand=1";
        $list = Db::query($sql);
        $ids = array();
        foreach ($list as &$vv) {
            $vv['total'] = db($tabName)->where(['province' => $vv['province']])->count();
            $ids[] = "'" . $vv['province'] . "'";
        }
        unset($vv);
        $sql1 = "select count(id) as total from crm_" . $tabName;
        if(count($ids)>0){
            $ids = implode(',', $ids);
            $sql1.=" where (province not in(" . $ids . ") or province is null) AND is_beforehand=1";
        }else{
            $sql1.=" where province is null AND is_beforehand=1";
        }
        $total1 = Db::query($sql1);
        $total = $total1[0]['total']; //st
        array_push($list, ['total' => $total, 'province' => '其它']);
        $allTotal=db($tabName)->count();
        return json(['code' => 1, 'msg' => '查询成功', 'data' => $list,'total'=>$allTotal]);
    }

    // 导出  stj ce
    public function export()
    {
        $exhibition_id = $this->request->param('exhibition_id');
        $project_id = $this->request->param('project_id');
        $start_time = $this->request->param('start_time'); //开始时间
        $end_time = $this->request->param('end_time'); //结束时间
        $channel_id = $this->request->param('channel_id'); //渠道来源
        $industrys = $this->request->param('industrys'); //用户类别
        $position_cn = $this->request->param('position_cn'); //用户职级
        $province = $this->request->param('province'); //区域
        $is_old = $this->request->param('is_old'); //是否新观众
        $keyword = $this->request->param('keyword'); //是否新观众
        if (empty($project_id)) {
            return json(['code' => 0, 'msg' => '缺少关键字段', 'data' => '']);
        }
        if (empty($exhibition_id)) {
            return json(['code' => 1, 'msg' => '展会数据错误', 'data' => ['list' => [], 'total' => 0]]);
        }
        $tabName = $this->getTabName($exhibition_id);
        //$allExList=db('exhibition')->where(['is_delete'=>0,'project_id'=>$project_id])->select();
        //查询展会里面的所有人员表格
        // $whereArr['is_delete'] = 0;
        // $whereArr['create_person_table'] = array('neq',$tabName);
        // $person_table=db('exhibition')->where($whereArr)->select();
        // $per_arr_name=array();
        // $per_arr_select=array();
        // $num=0;
        // foreach ($person_table as $v){
        //    $per_arr_name[]='crm_'.$v['create_person_table']." as z".$num;
        //    $per_arr_select[]="z".$num.".id as id".$num.", z".$num.".company_name as company_name".$num;
        //    $num++;
        // }
        // $per_tab_name=implode(',',$per_arr_name);
        // $per_select_param=implode(',',$per_arr_select);

        $param = " g.is_beforehand=1 ";
        if (!empty($start_time) && !empty($end_time)) {
            $start_year = strtotime($start_time . " 00:00:00");
            $end_year = strtotime($end_time . " 23:59:59");
            $param .= " and g.create_time>=$start_year and g.create_time<=$end_year ";
        }

        if (!empty($channel_id)) {
            if ($channel_id == '-1') {
                $param .= " and g.channel_id is null ";
            } else {
                $param .= " and g.channel_id=$channel_id ";
            }
        }

        if (!empty($industrys)) {
            if ($industrys == '-1') {
                $param .= " AND g.industrys is null ";
            } else {
                $param .= " and g.industrys like '%$industrys%' ";
            }
        }

        if (!empty($position_cn)) {
            if ($position_cn == '-1') {
                $param .= " AND g.positions_cn is null ";
            } else {
                $param .= " and g.positions_cn like '%$position_cn%' ";
            }
        }

        if (!empty($province)) {
            if ($province == '-1') {
                $param .= " AND g.province is null ";
            } else {
                $param .= " and g.province like '%$province%' ";
            }
        }

        if ($is_old > -1) {
            $param .= " and g.is_old=$is_old ";
        }

        if (!empty($keyword)) {
            $param .= " and (g.company_name like '%$keyword%' or g.name_cn like '%$keyword%') ";
        }
        $sql = "select g.*,q.title as channel_title,b.name_cn as parent_name from crm_" . $tabName . " as g ";
        $sql .= " left join crm_zsqudao as q on q.id=g.channel_id";
        $sql .= " left join crm_company_user as b on b.id=g.parent_id";
        $sql .= " where 1=1 $param ";
        $sql .= " order by g.id desc";
        $list = Db::query($sql);
        foreach ($list as &$v) {
            $v['create_time'] = date('Y-m-d H:i:s', $v['create_time']);
            $v['no_info'] = '';
            $formData = '本站';
            if (!empty($v['channel_title'])) {
                $formData = $v['channel_title'];
            } else {
                if (!empty($v['parent_id'])) {
                    $formData = $v['parent_name'];
                    $v['qd_type'] = '观众';
                }
            }
            $v['user_form_data'] = $formData;
            $v['examine_type_str'] = '待审核';
            if ($v['examine_type'] == 1) {
                $v['examine_type_str'] = '审核通过';
            }
            if ($v['examine_type'] == 3) {
                $v['examine_type_str'] = '审核未通过';
            }
        }
        $columns = array(
            array('title' => 'ID', 'field' => 'id', 'width' => 12),
            array('title' => '姓名', 'field' => 'name_cn', 'width' => 12),
            array('title' => '英文姓名', 'field' => 'name_en', 'width' => 12),
            array('title' => '称谓', 'field' => 'surname_cn', 'width' => 12),
            array('title' => 'QQ号', 'field' => 'qq', 'width' => 12),
            array('title' => '微信OpenID', 'field' => 'wechat', 'width' => 12),
            array('title' => '观众类别', 'field' => 'member_type', 'width' => 12),
            array('title' => '手机', 'field' => 'mobile_person', 'width' => 12),
            array('title' => '身份证/护照号码', 'field' => 'id_side', 'width' => 12),
            array('title' => '电子邮件', 'field' => 'email', 'width' => 12),
            array('title' => '电话', 'field' => 'tel', 'width' => 12),
            array('title' => '传真', 'field' => 'fax_person', 'width' => 12),
            array('title' => '国籍', 'field' => 'country_name', 'width' => 12),
            array('title' => '国家', 'field' => 'country', 'width' => 12),
            array('title' => '省份', 'field' => 'province', 'width' => 24),
            array('title' => '城市', 'field' => 'area_city', 'width' => 24),
            array('title' => '详细地址', 'field' => 'area', 'width' => 24),
            array('title' => '英文地址', 'field' => 'area_en', 'width' => 24),
            array('title' => '公司名称', 'field' => 'company_name', 'width' => 12),
            array('title' => '公司名称英文', 'field' => 'company_name_en', 'width' => 12),
            array('title' => '行业', 'field' => 'industrys', 'width' => 24),
            array('title' => '部门', 'field' => 'department_cn', 'width' => 24),
            array('title' => '职位', 'field' => 'positions_cn', 'width' => 24),
            array('title' => '注册时间', 'field' => 'create_time', 'width' => 24),
            array('title' => '数据来源', 'field' => 'user_form_data', 'width' => 24),
            array('title' => '卡号', 'field' => 'is_cart', 'width' => 24),
            array('title' => '邀请码', 'field' => 'registration_no', 'width' => 24),
            array('title' => '邀请人类型', 'field' => 'qd_type', 'width' => 24),
            array('title' => '备注', 'field' => 'no_info', 'width' => 24),
            array('title' => '是否支付会议款项', 'field' => 'no_info', 'width' => 24),
            array('title' => '负责人', 'field' => 'no_info', 'width' => 24),
            array('title' => 'E展通注册方式', 'field' => 'no_info', 'width' => 24),
            array('title' => '支付金额', 'field' => 'no_info', 'width' => 24),
            array('title' => '预登记邀请', 'field' => 'examine_type_str', 'width' => 24),
            array('title' => '参加活动', 'field' => 'examine_type_str', 'width' => 24),
            array('title' => '登录密码', 'field' => 'no_str', 'width' => 24),
            array('title' => '邮编', 'field' => 'no_str', 'width' => 24),
            array('title' => '网址', 'field' => 'link', 'width' => 24),
            array('title' => '审核', 'field' => 'examine_type_str', 'width' => 24),
        );
        $title = "观众统计明细";
        $filename = $title . date('Y-m-d-H-i', time()) . ".xls";
        return json(['code' => 1, 'msg' => '操作成功', 'data' => ['columns' => $columns, 'filename' => $filename, 'list' => $list]]);
    }



    //今日数量
    public function getToday()
    {
        $exhibition_id = $this->request->param('exhibition_id');
        if (empty($exhibition_id)) {
            return json(['code' => 0, 'msg' => '缺少关键字段', 'data' => '']);
        }
        $date = $this->request->param('date');
        $start_time = '';
        $end_time = '';
        if (empty($date)) {
            $start_time = strtotime(time('Y-m-d') . " 00:00:00");
            $end_time = strtotime(time('Y-m-d') . " 23:59:59");
        } else {
            $start_time = strtotime($date . " 00:00:00");
            $end_time = strtotime($date . " 23:59:59");
        }
        $tabName = $this->getTabName($exhibition_id);
        $sql = "SELECT * FROM crm_" . $tabName . "  WHERE create_time<=" . $end_time . " and create_time>=" . $start_time.' AND is_beforehand=1';
        $list = Db::query($sql);
        return json(['code' => 1, 'msg' => '查询成功', 'data' => $list]);
    }

    //今日数量
    public function getWeek()
    {
        $exhibition_id = $this->request->param('exhibition_id');
        if (empty($exhibition_id)) {
            return json(['code' => 0, 'msg' => '缺少关键字段', 'data' => '']);
        }
        $date = $this->request->param('date');
        $start_time = '';
        $end_time = '';
        if (empty($date)) {
            $start_time = strtotime(date('Y-m-d') . " 00:00:00");
            $end_time = strtotime(date('Y-m-d') . " 23:59:59");
        } else {
            $start_time = strtotime($date . " 00:00:00");
            $end_time = strtotime($date . " 23:59:59");
        }
        $tabName = $this->getTabName($exhibition_id);
        $sql = "SELECT * FROM crm_" . $tabName . "  WHERE create_time<=" . $end_time . " and create_time>=" . $start_time.' AND is_beforehand=1';
        $list = Db::query($sql);
        return json(['code' => 1, 'msg' => '查询成功', 'data' => $list]);
    }

    //获取所有周
    public function getAllWeekList(){
        $exhibition_id = $this->request->param('exhibition_id');
        if (empty($exhibition_id)) {
            return json(['code' => 0, 'msg' => '缺少关键字段', 'data' => '']);
        }
        $tabName = $this->getTabName($exhibition_id);
        $sql = "SELECT create_time FROM crm_" . $tabName . " WHERE create_time>0 order by create_time desc limit 0,1 ";
        $sql1 = "SELECT create_time FROM crm_" . $tabName . " WHERE create_time>0 order by create_time asc limit 0,1 ";
        $list = Db::query($sql);
        $list1 = Db::query($sql1);
        $weekList=$this->getAllWeek($list1[0]['create_time'],$list[0]['create_time']);
        foreach ($weekList as &$vv) {
            $eachSql="select count(id) as each_total from crm_".$tabName." where create_time>=".strtotime($vv['start_time'] . " 00:00:00")." and create_time<=".strtotime($vv['end_time'] . " 23:59:59");
            $totals = Db::query($eachSql);
            $vv['total'] = $totals[0]['each_total'];
        }
        return json(['code' => 1, 'msg' => '查询成功', 'data' => $weekList]);
    }


    //提取周
    public function getAllWeek($startdate, $enddate)
    {
        //参数不能为空
        if (!empty($startdate) && !empty($enddate)) {
            //开始日期不能大于结束日期
            if ($startdate <= $enddate) {
                $end_date = strtotime("next monday", $enddate);
                if (date("w", $startdate) == 1) {
                    $start_date = $startdate;
                } else {
                    $start_date = strtotime("last monday", $startdate);
                }
                //计算时间差多少周
                $countweek = ($end_date - $start_date) / (7 * 24 * 3600);
                for ($i = 0; $i < $countweek; $i++) {
                    $sd = date("Y-m-d", $start_date);
                    $ed = strtotime("+ 6 days", $start_date);
                    $eed = date("Y-m-d", $ed);
                    $arr[] = array('name'=>'第'.($i+1).'周','start_time'=>$sd, 'end_time'=>$eed);
                    $start_date = strtotime("+ 1 day", $ed);
                }
                return $arr;
            }
        }else{
            return [];
        }
    }

    //获取所有周
    public function getAllMonthList(){
        $exhibition_id = $this->request->param('exhibition_id');
        if (empty($exhibition_id)) {
            return json(['code' => 0, 'msg' => '缺少关键字段', 'data' => '']);
        }
        $tabName = $this->getTabName($exhibition_id);
        $sql = "SELECT create_time FROM crm_" . $tabName . " WHERE create_time>0 order by create_time desc limit 0,1 ";
        $sql1 = "SELECT create_time FROM crm_" . $tabName . " WHERE create_time>0  order by create_time limit 0,1 ";
        $list = Db::query($sql);
        $list1 = Db::query($sql1);
        $monthist=$this->showMonthRange(date('Y-m-d',$list1[0]['create_time']),date('Y-m-d',$list[0]['create_time']));
        foreach ($monthist as &$vv) {
            $eachSql="select count(id) as each_total from crm_".$tabName." where create_time>=".strtotime($vv['startDate'] . " 00:00:00")." and create_time<=".strtotime($vv['endDate'] . " 23:59:59");
            $totals = Db::query($eachSql);
            $vv['total'] = $totals[0]['each_total'];
        }
        return json(['code' => 1, 'msg' => '查询成功', 'data' => $monthist]);
    }

    //提取月
    public function showMonthRange($startDate, $endDate)
    {
        $tempDate = $startDate;
        $returnData = [];
        $i = 0;
        while (strtotime($tempDate) < strtotime($endDate)) {
            $temp = [];
            $month = strtotime('first day of +' . $i . ' month', strtotime($startDate));
            $temp['name'] = date('Y-m', $month);
            $temp['startDate'] = date('Y-m-01', $month);
            $temp['endDate'] = date('Y-m-t', $month);
            $tempDate = $temp['endDate'];
            $returnData[] = $temp;
            $i++;
        }
        return $returnData;
    }

    public function homeInfo(){
        $exhibition_id =$this->request->param('exhibition_id');
        if(empty($exhibition_id)){
            return json(['code'=>0,'msg'=>'缺少关键字段','data'=>'']);
        }
        $tabName=$this->getTabName($exhibition_id);

        //渠道
        $sql="SELECT count(DISTINCT channel_id) as total FROM crm_".$tabName." where is_beforehand=1";
        $channel_total=Db::query($sql);

        //职务
        $sql1="SELECT count(DISTINCT positions_cn) as total FROM crm_".$tabName." where is_beforehand=1";
        $positions_cn_total=Db::query($sql1);

        //观众类别 industrys
        $sql2="SELECT count(DISTINCT industrys) as total FROM crm_".$tabName." where is_beforehand=1";
        $industrys_total=Db::query($sql2);

        //省份 province
        $sql3="SELECT count(DISTINCT province) as total FROM crm_".$tabName." where is_beforehand=1";
        $province_total=Db::query($sql3);

        //今日
        $start_time = strtotime(date('Y-m-d') . " 00:00:00");
        $end_time = strtotime(date('Y-m-d') . " 23:59:59");
        $sql4 = "SELECT count(id) as total FROM crm_" . $tabName . "  WHERE create_time<=" . $end_time . " and create_time>=" . $start_time." AND is_beforehand=1";
        $day_total = Db::query($sql4);
        //本周
        $week=$this->aweek();
        $sql5 = "SELECT count(id) as total FROM crm_" . $tabName . "  WHERE create_time<=" . $week[1] . " and create_time>=" . $week[0]." AND is_beforehand=1";
        $week_total = Db::query($sql5);
        //本月
        $sql6 = "SELECT count(id) as total FROM crm_" . $tabName . "  WHERE create_time<=" . mktime(0,0,0,date('m'),date('t'),date('Y')) . " and create_time>=" . mktime(0,0,0,date('m'),1,date('Y'))." AND is_beforehand=1";
        $mouth_total = Db::query($sql6);
        return json(['code' => 1, 'msg' => '查询成功', 'data' => ['channel_total'=> $channel_total[0]['total'],'positions_cn_total'=> $positions_cn_total[0]['total'],'industrys_total'=> $industrys_total[0]['total'],'province_total'=> $province_total[0]['total'],'day_total'=> $day_total[0]['total'],'week_total'=> $week_total[0]['total'],'mouth_total'=> $mouth_total[0]['total']]]);
    }

    public function aweek($gdate = "", $first = 0){
        if(!$gdate) $gdate = date("Y-m-d");
        $w = date("w", strtotime($gdate));//取得一周的第几天,星期天开始0-6
        $dn = $w ? $w - $first : 6;//要减去的天数
        //本周开始日期
        $st = strtotime("$gdate -".$dn." days");
        //本周结束日期
        $stt=date("Y-m-d", strtotime("$gdate -".$dn." days"));
        $en = strtotime("$stt +6 days");
        return array($st+86400, $en+(86400*2-1));//返回开始和结束日期
       }
}
